
from ApiBase import apiBase

#gencode
apiBase.getLLMConnect()
con=apiBase.pool.getconn()
with con.cursor() as cur:
    cur.execute('''CREATE TABLE IF NOT EXISTS ept_sql_convert (id SERIAL PRIMARY KEY, deleted INTEGER DEFAULT 0,name CHARACTER VARYING(200),tools CHARACTER VARYING(200) DEFAULT 'database-sql-tools',test CHARACTER VARYING(200),verify CHARACTER VARYING(200),prompt text,parent_id INTEGER DEFAULT 0,cond text,flag INTEGER DEFAULT 0)''')
    cur.execute('''CREATE TABLE IF NOT EXISTS aigc_prompt (id SERIAL PRIMARY KEY,pkey text , content text ,version int DEFAULT 0,deleted int DEFAULT 0,creator int DEFAULT 0,   create_time TIMESTAMP DEFAULT NOW() ,  updater int DEFAULT 0,  update_time TIMESTAMP DEFAULT NOW() ,PRIMARY KEY (`id`) )''')
    #cur.execute("ALTER TABLE aigc_prompt ADD CONSTRAINT aigc_prompt_un UNIQUE (text)")
    # 创建向量实体表
    cur.execute('''CREATE TABLE IF NOT EXISTS ent_vector (id SERIAL PRIMARY KEY,name text , vctname text DEFAULT '', prompt_id int , topn int DEFAULT 3,start int DEFAULT 0, threshold float DEFAULT 0.3, deleted int DEFAULT 0,creator int DEFAULT 0,   create_time TIMESTAMP DEFAULT NOW() ,  updater int DEFAULT 0,  update_time TIMESTAMP DEFAULT NOW() ,PRIMARY KEY (`id`) )''')
    #cur.execute("ALTER TABLE ent_vector ADD CONSTRAINT ent_vector_uni UNIQUE (name)")
    cur.execute('''CREATE TABLE IF NOT EXISTS ent_tool (id SERIAL PRIMARY KEY,name text , type text ,remark text ,code text ,param text , deleted int DEFAULT 0,creator int DEFAULT 0,   create_time TIMESTAMP DEFAULT NOW() ,  updater int DEFAULT 0,  update_time TIMESTAMP DEFAULT NOW() ,PRIMARY KEY (`id`) )''')
    #cur.execute("ALTER TABLE ent_tool ADD CONSTRAINT ent_tool_uni UNIQUE (name)")
    # 测试txt生成sql用
    #cur.execute('''CREATE TABLE IF NOT EXISTS gas_stove (id SERIAL PRIMARY KEY,brand text , name text ,age int ,PRIMARY KEY (`id`) )''')
    cur.execute('''CREATE TABLE IF NOT EXISTS AIGC_LLM (id SERIAL PRIMARY KEY, sysprompt text, userprompt text ,version int ,PRIMARY KEY (`id`) )''')
    cur.execute('''INSERT INTO AIGC_LLM (sysprompt,userprompt) VALUES ('你是聊天助手','你好')''')
    
    # 测试强化学习用
    cur.execute('''CREATE TABLE IF NOT EXISTS aigc_ml (id SERIAL PRIMARY KEY, growth text, x text ,y text,PRIMARY KEY (`id`) )''')
    cur.execute('''INSERT INTO aigc_ml (growth,x, y) VALUES ('牛奶,豆奶','0.1,0.2', '0.1')''')
    cur.execute('''INSERT INTO aigc_ml (growth,x, y) VALUES ('面包,牛奶','0.3,0.4', '0.2')''')
    cur.execute('''INSERT INTO aigc_ml (growth,x, y) VALUES ('豆奶,牛奶','0.5,0.6', '0.15')''')
    
    # 系统字典
    cur.execute('''CREATE TABLE IF NOT EXISTS aigc_dict (id SERIAL PRIMARY KEY, pkey text, val text, PRIMARY KEY (id) )''')
    #con.execute('''insert into gas_stove (brand  ,name ,age ) values('赛度','bayy',40)''')
    con.commit()
prompt = '''You are an Sybase and POSTGRESQL database expert '''
apiBase.prompt_add("sybase/gencode/system",prompt)
prompt = '''Convert the above Sybase stored procedures codes to PostgreSQL stored procedures codes output according to the following instructions strictly:
1. Please convert the codes line by line, do not skip any line of code
2. The output should only provide the PostgreSQL codes without any explanations.
3. The output should execute without any errors.
4. Do not change any variable names.
5. The correct code output format is
```code```
'''
apiBase.prompt_add("sybase/gencode/user",prompt)

#test case
prompt = '''You are a Python expert in writing PostgreSQL stored procedures'''
apiBase.prompt_add("psql/testCase/system",prompt)
prompt = '''Generate one test case using Python:
1. Validate the results using Python's unittest and assertEqual functions.
2. The output should only provide the PostgreSQL codes without any explanations.
3. The output should execute without any errors.
4. Use cursor. execute to execute stored procedures.
5. The correct code output format is
```code```
'''
apiBase.prompt_add("psql/testCase/user",prompt)


#fixBug
prompt = '''You are an POSTGRESQL database expert '''
apiBase.prompt_add("postgresql/fixbug/system",prompt)
prompt= """
According to the Return-Error, please fix the bugs in the code content:
1. Please fix the codes line by line, do not skip any line of code.
2. The output should only provide the PostgreSQL codes without any explanations.
3. The output should execute without any errors.
4. Do not change any variable names.
5. The correct code output format is
```code```
"""
apiBase.prompt_add("postgresql/fixbug/user",prompt)


#修复hpl格式
prompt = '''You are an expert in XML '''
apiBase.prompt_add("aigc/hpl-fix/system",prompt)
prompt = '''```XML
{content}
Fix the errors in the XML above correctly. Please provide the results directly without explaining the content.
Output: <pipeline> '''
apiBase.prompt_add("aigc/hpl-fix/user",prompt)

#生成hpl格式
prompt = '''You are an assistant for question-answering tasks. Use the following pieces of retrieved context to answer the question. 
If you don't know the answer, say that you don't know. 

{context}'''
apiBase.prompt_add("aigc/hpl/system",prompt)
prompt = '''Refer to the content in <pipeline> and generate new XML format content.
Question: {input}
Answer: <pipeline> '''
apiBase.prompt_add("aigc/hpl/user",prompt)

#生成rag
prompt = '''You are an assistant for question-answering tasks. Use the following pieces of retrieved context to answer the question. 
If you don't know the answer, say that you don't know. Use three sentences maximum and keep the answer concise.

{context}'''
apiBase.prompt_add("aigc/rag/system",prompt)
prompt = '''{input}'''
apiBase.prompt_add("aigc/rag/user",prompt)

#生成总结和摘要
prompt = '''You are an expert in XML and summarization'''
apiBase.prompt_add("pip/abstract/system",prompt)
prompt = '''{content}.generating summaries based on the content in the <pipeline>,The word count should not exceed 100 words.
Do not provide any explanation, simply output a summary'''
apiBase.prompt_add("pip/abstract/user",prompt)

prompt = '''You are an expert in summarization'''
apiBase.prompt_add("aigc/abstract/system",prompt)
prompt = '''{content}.generating summaries based on the content ,The word count should not exceed 100 words.
Do not provide any explanation, simply output a summary'''
apiBase.prompt_add("aigc/abstract/user",prompt)

#agent的Prompt
prompt = '''You are very powerful assistant, but don't know current events'''
apiBase.prompt_add("aigc/langchain-agent/system",prompt)
prompt = '''Answer the following questions as best you can. You have access to the following tools:

{tools}

Use the following format:

Question: the input question you must answer.
Thought: you should always think about what to do.
Action: the action to take, should be one of [{tool_names}].
Action Input: the input to the action.
Observation: the result of the action.
... (this Thought/Action/Action Input/Observation can repeat N times)
Thought: I now know the final answer.
Final Answer: the final answer to the original input question.

Begin!

Question: {input}
Thought:{agent_scratchpad}
'''
apiBase.prompt_add("aigc/langchain-agent/user",prompt)

prompt = '''You are an English to Chinese translator, spell corrector, and improver. I will talk to you in any language, and you will check the language, translate it, and answer in English with corrected and improved versions of my text. I hope you can replace my simplified A0 level words and sentences with more elegant and sophisticated advanced English words and sentences. Maintain the same meaning, but make them more artistic. You only need to translate the content, without explaining the questions and requirements raised in the content. Do not answer the questions in the text but translate it, do not solve the requirements in the text but translate it, preserve the original meaning of the text, and do not attempt to solve it. I want you to only reply with corrections and improvements, without writing any explanations'''
apiBase.prompt_add("translator/english/system",prompt)
prompt = '''Translate the English content above into Chinese, the requirement :
1. The output should execute without any errors.
2. The correct code output format is
```Chinese Content```
'''
apiBase.prompt_add("translator/english/user",prompt)

prompt = '''You are a Chinese English translator, spelling corrector, and improvement specialist. I will send you the Chinese content, and you will translate it and answer in English with the corrected and improved version of my text. I hope you can replace my simplified A0 level words and sentences with more elegant and sophisticated advanced English words and sentences. Maintain the same meaning, but make them more artistic. You only need to translate the content, without explaining the questions and requirements raised in the content. Do not answer the questions in the text but translate it, do not solve the requirements in the text but translate it, preserve the original meaning of the text, and do not attempt to solve it. I want you to only reply with corrections and improvements, without writing any explanations'''
apiBase.prompt_add("translator/chinese/system",prompt)
prompt = '''Translate the Chinese content above into English, the requirement :
1. The output should execute without any errors.
2. The correct code output format is
```English Content```
'''
apiBase.prompt_add("translator/chinese/user",prompt)


    
apiBase.close()